Searching Patterns Using LIKE and Wildcards in MySQL – Detailed Notes

Course: MySQL Training

Topic: Searching Patterns Using LIKE and Wildcards

Course Link: MySQL Training Course

Demo Link: Register for a Course Demo

---

1. Introduction to Pattern Searching in MySQL

Pattern searching is an important feature of MySQL that allows users to retrieve records by matching a specific text pattern rather than searching for an exact value. It is useful when the complete value is unknown or when you need to find text that begins with, ends with, or contains particular characters.

For example, a company may want to find employees whose names start with the letter A, customers whose email addresses belong to a particular domain, products containing the word "phone", or records whose product codes follow a particular format.

MySQL provides the LIKE operator for simple pattern matching. It works with two main wildcard characters: the percent sign (%) and the underscore (_).

• % wildcard: Matches zero or more characters.
• _ wildcard: Matches exactly one character.

These wildcards make it possible to search text without knowing every character in advance. LIKE is generally used with the WHERE clause to filter query results.

Example

SELECT employee_name
FROM employees
WHERE employee_name LIKE 'A%';

This query retrieves employee names that begin with A, subject to the column's collation and comparison rules.

2. Learning Objectives

After completing this topic, you will be able to:

• Understand pattern matching in MySQL.
• Explain the purpose and syntax of the LIKE operator.
• Use the percent (%) wildcard to match variable-length text.
• Use the underscore (_) wildcard to match a single character.
• Search for text beginning with, ending with, or containing a particular string.
• Combine LIKE with AND, OR, and NOT LIKE.
• Search for literal percent and underscore characters using escaping.
• Apply LIKE to employee, customer, product, and email data.
• Understand case sensitivity, NULL values, and performance considerations.
• Distinguish LIKE pattern matching from exact matching and regular expressions.

3. What Is the LIKE Operator?

The LIKE operator checks whether a string matches a specified SQL pattern. Unlike the equality operator (=), which compares values for equality according to SQL comparison rules, LIKE interprets the percent and underscore characters as pattern-matching wildcards.

General Syntax

SELECT column1, column2
FROM table_name
WHERE column_name LIKE 'pattern';

Syntax Explanation

Component	Description
SELECT	Specifies the columns to retrieve.
FROM	Identifies the table being searched.
WHERE	Filters records according to a condition.
column_name	The column whose text is being searched.
LIKE	Checks whether the value matches a pattern.
pattern	The text pattern containing optional wildcards.

Example

SELECT *
FROM customers
WHERE customer_name LIKE 'Rahul%';

This query returns customers whose names begin with Rahul, such as Rahul Sharma or Rahul Verma, depending on the stored values and collation rules.

4. Understanding Wildcards in MySQL

A wildcard is a special character that represents one or more characters during a pattern search. MySQL's LIKE operator supports two primary wildcard characters.

Wildcard	Meaning	Example Pattern	Example Matches
%	Zero or more characters	'A%'	A, Amit, Ananya
_	Exactly one character	'A_it'	Abit, Amit
%text%	Text anywhere within the value	'%phone%'	mobile phone, phone case
text%	Text at the beginning	'MySQL%'	MySQL Training
%text	Text at the end	'%gmail.com'	[email protected]

Important: LIKE compares the pattern against the entire string. The % wildcard allows other characters before or after the text when needed. Without wildcards, LIKE generally requires the whole string to match the supplied pattern.

5. Creating a Sample Database and Table

To practise LIKE and wildcards, create a sample database containing employee information.

Step 1: Create a Database

CREATE DATABASE company_db;

Step 2: Select the Database

USE company_db;

Step 3: Create an Employees Table

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department VARCHAR(50),
    email VARCHAR(150),
    city VARCHAR(80),
    job_title VARCHAR(100)
);

Step 4: Insert Sample Data

INSERT INTO employees
(employee_id, employee_name, department, email, city, job_title)
VALUES
(1, 'Amit Sharma', 'IT', '[email protected]', 'Mumbai', 'Software Developer'),
(2, 'Ananya Patel', 'HR', '[email protected]', 'Pune', 'HR Executive'),
(3, 'Rahul Verma', 'IT', '[email protected]', 'Mumbai', 'Database Developer'),
(4, 'Priya Singh', 'Finance', '[email protected]', 'Delhi', 'Accountant'),
(5, 'Arjun Mehta', 'Sales', '[email protected]', 'Nashik', 'Sales Executive'),
(6, 'Neha Joshi', 'IT', '[email protected]', 'Pune', 'Web Developer'),
(7, 'Rohan Gupta', 'Marketing', '[email protected]', 'Mumbai', 'Marketing Manager'),
(8, 'Sneha Rao', 'HR', '[email protected]', 'Bengaluru', 'HR Manager'),
(9, 'Aarav Desai', 'IT', '[email protected]', 'Mumbai', 'Software Tester'),
(10, 'Meera Shah', 'Finance', '[email protected]', 'Pune', 'Financial Analyst');

Step 5: Display All Records

SELECT * FROM employees;

This table will be used in the following examples to demonstrate pattern searching.

6. Searching Values That Begin with Specific Characters

To find values that begin with a particular character or word, write the required text followed by the percent wildcard (%).

Syntax

WHERE column_name LIKE 'text%';

Example 1: Names Beginning with A

SELECT employee_name
FROM employees
WHERE employee_name LIKE 'A%';

This query retrieves employees whose names begin with A, such as Amit Sharma, Ananya Patel, and Aarav Desai, when the applicable collation matches the pattern without regard to case.

Example 2: Employees Whose Names Begin with Rahul

SELECT employee_id, employee_name
FROM employees
WHERE employee_name LIKE 'Rahul%';

This query searches for employee names starting with Rahul. The % wildcard permits any characters after Rahul, including no additional characters.

Example 3: Email Addresses Beginning with a

SELECT employee_name, email
FROM employees
WHERE email LIKE 'a%';

This query retrieves email addresses whose first character matches a according to the column's comparison rules.

Common Uses

• Finding customers by the beginning of their names.
• Searching product codes with a shared prefix.
• Filtering email addresses by a starting string.
• Finding categories that begin with a particular word.

7. Searching Values That End with Specific Characters

To find values that end with a particular character or text, place the percent wildcard (%) before the required text.

Syntax

WHERE column_name LIKE '%text';

Example 1: Email Addresses Ending with gmail.com

SELECT employee_name, email
FROM employees
WHERE email LIKE '%gmail.com';

This query retrieves employee records whose email addresses end with gmail.com.

Example 2: Names Ending with a

SELECT employee_name
FROM employees
WHERE employee_name LIKE '%a';

This query searches for employee names whose complete stored values end with the character a.

Example 3: Job Titles Ending with Developer

SELECT employee_name, job_title
FROM employees
WHERE job_title LIKE '%Developer';

This query retrieves job titles that end with Developer, such as Software Developer or Database Developer.

Common Uses

• Finding email addresses with a specific domain.
• Searching product identifiers with a shared suffix.
• Filtering job titles ending with a particular term.
• Finding filenames with a particular ending when filenames are stored in a database.

8. Searching Values That Contain Specific Text

To find a string containing a word or character sequence anywhere in the value, place % on both sides of the required text.

Syntax

WHERE column_name LIKE '%text%';

Example 1: Names Containing the Text 'ha'

SELECT employee_name
FROM employees
WHERE employee_name LIKE '%ha%';

This query returns employee names that contain the sequence ha anywhere in the stored value.

Example 2: Job Titles Containing Developer

SELECT employee_name, job_title
FROM employees
WHERE job_title LIKE '%Developer%';

This query finds job titles containing Developer, whether the word appears at the beginning, middle, or end.

Example 3: Cities Containing 'ba'

SELECT employee_name, city
FROM employees
WHERE city LIKE '%ba%';

This query searches for city names containing the character sequence ba.

Common Uses

• Searching product descriptions for a keyword.
• Finding employee job titles containing a particular role.
• Searching customer records by a partial name.
• Finding text in comments or descriptions.

Performance note: A pattern such as '%text%' often cannot use a conventional B-tree index for a direct range lookup because the pattern begins with a wildcard. On large tables, consider full-text search or another search strategy when appropriate.

9. Using the Underscore (_) Wildcard

The underscore (_) wildcard matches exactly one character. It is useful when you know the position of a character or need to match values with a particular character length or structure.

Example 1: Match a Four-Character Name Pattern

SELECT employee_name
FROM employees
WHERE employee_name LIKE 'A___';

The pattern matches a string of exactly four characters that starts with A. Each underscore represents one character. This example is most useful with short names or codes because the employee_name values in the sample table contain full names.

Example 2: Match a Pattern with a Known First and Last Character

SELECT employee_name
FROM employees
WHERE employee_name LIKE 'A_it';

This pattern matches a five-character string beginning with A, followed by any single character, then it. For example, the standalone value Amit would match the pattern 'A_it'.

Example 3: Find Product Codes with a Fixed Format

SELECT product_code
FROM products
WHERE product_code LIKE 'AB_123';

This example assumes a products table with a product_code column. The pattern matches codes starting with AB, followed by exactly one character, followed by 123.

Example 4: Match a Specific Number of Characters

SELECT code
FROM product_codes
WHERE code LIKE '_____';

This query matches values containing exactly five characters under the applicable string comparison rules. Each underscore represents one character.

10. Combining Percent and Underscore Wildcards

You can combine % and _ in a single pattern to create more flexible searches.

Example 1: Values Beginning with A and Having at Least One More Character

SELECT employee_name
FROM employees
WHERE employee_name LIKE 'A_%';

The pattern requires A followed by at least one character, because the underscore matches one character and % can match any number of additional characters, including zero.

Example 2: Find Codes Beginning with PR and Ending with 9

SELECT product_code
FROM products
WHERE product_code LIKE 'PR%9';

This pattern matches product codes that begin with PR and end with 9, with any number of characters between those parts.

Example 3: Find a Pattern with Two Characters After a Prefix

SELECT product_code
FROM products
WHERE product_code LIKE 'AB__';

This pattern matches values containing exactly four characters: AB followed by two characters.

Example 4: Find Text with a Required Character Position

SELECT employee_name
FROM employees
WHERE employee_name LIKE '_a%';

This query finds names whose second character matches a, followed by any number of additional characters. Results depend on the column's collation.

11. Understanding NOT LIKE

The NOT LIKE operator returns records whose values do not match a specified pattern. It is useful when you need to exclude names, domains, prefixes, suffixes, or text patterns.

Syntax

SELECT column_name
FROM table_name
WHERE column_name NOT LIKE 'pattern';

Example 1: Exclude Names Beginning with A

SELECT employee_name
FROM employees
WHERE employee_name NOT LIKE 'A%';

This query retrieves employee names that do not match the pattern beginning with A.

Example 2: Exclude Gmail Addresses

SELECT employee_name, email
FROM employees
WHERE email NOT LIKE '%gmail.com';

This query returns email addresses that do not end with gmail.com. It does not return rows where email is NULL, because NOT LIKE with NULL evaluates to NULL rather than TRUE.

Example 3: Exclude Job Titles Containing Manager

SELECT employee_name, job_title
FROM employees
WHERE job_title NOT LIKE '%Manager%';

This query excludes job titles containing Manager according to the comparison rules in effect.

12. Combining LIKE with AND

AND allows multiple conditions to be applied together. A row must satisfy every condition to appear in the result.

Example 1: Names Beginning with A and Located in Mumbai

SELECT employee_name, city
FROM employees
WHERE employee_name LIKE 'A%'
  AND city = 'Mumbai';

This query returns employees whose names begin with A and whose city is Mumbai.

Example 2: IT Employees Whose Job Titles Contain Developer

SELECT employee_name, department, job_title
FROM employees
WHERE department = 'IT'
  AND job_title LIKE '%Developer%';

This query filters the IT department and then matches job titles containing Developer.

Example 3: Gmail Addresses Beginning with a Specific Name Prefix

SELECT employee_name, email
FROM employees
WHERE email LIKE 'amit%'
  AND email LIKE '%gmail.com';

This query requires the email to match both the prefix pattern and the ending pattern. Case matching depends on the column's collation.

13. Combining LIKE with OR

OR allows a record to match at least one of the conditions.

Example 1: Names Beginning with A or R

SELECT employee_name
FROM employees
WHERE employee_name LIKE 'A%'
   OR employee_name LIKE 'R%';

This query returns employee names beginning with A or R.

Example 2: Search Multiple Job Title Patterns

SELECT employee_name, job_title
FROM employees
WHERE job_title LIKE '%Developer%'
   OR job_title LIKE '%Tester%';

This query retrieves employees whose job titles contain Developer or Tester.

Example 3: Search Emails from Two Domains

SELECT employee_name, email
FROM employees
WHERE email LIKE '%gmail.com'
   OR email LIKE '%yahoo.com';

This query finds email addresses ending in either of the two specified domains. For strict domain validation, also consider whether the stored value follows the exact email format required by your application.

14. Using LIKE with ORDER BY

LIKE filters records, while ORDER BY sorts the matching results.

Example 1: Names Beginning with A in Alphabetical Order

SELECT employee_name
FROM employees
WHERE employee_name LIKE 'A%'
ORDER BY employee_name ASC;

Example 2: Sort Matching Job Titles

SELECT employee_name, job_title
FROM employees
WHERE job_title LIKE '%Developer%'
ORDER BY job_title ASC;

These queries return matching rows sorted by the requested column.

15. Using LIKE with DISTINCT

DISTINCT removes duplicate values from a result. Combining it with LIKE is useful when you want unique values that match a particular pattern.

Example: Unique Cities Beginning with M

SELECT DISTINCT city
FROM employees
WHERE city LIKE 'M%';

This query returns unique city names that begin with M.

Example: Unique Job Titles Containing Developer

SELECT DISTINCT job_title
FROM employees
WHERE job_title LIKE '%Developer%';

This query displays each matching job title only once.

16. Using LIKE with COUNT()

COUNT() calculates how many rows satisfy a condition. LIKE can be used in the WHERE clause to count records matching a text pattern.

Example 1: Count Names Beginning with A

SELECT COUNT(*) AS employee_count
FROM employees
WHERE employee_name LIKE 'A%';

Example 2: Count Gmail Addresses

SELECT COUNT(*) AS gmail_count
FROM employees
WHERE email LIKE '%gmail.com';

Example 3: Count Job Titles Containing Developer

SELECT COUNT(*) AS developer_count
FROM employees
WHERE job_title LIKE '%Developer%';

The result is a single count showing how many rows match the specified pattern.

17. Searching for Literal Percent (%) and Underscore (_) Characters

In LIKE patterns, % and _ have special meanings. If you need to search for a percent sign or underscore that is actually stored in the data, you must escape that character so MySQL treats it as a literal character rather than a wildcard.

Example 1: Find Product Codes Containing an Underscore

SELECT product_code
FROM products
WHERE product_code LIKE '%\_%';

With the default backslash escape behavior, the escaped underscore represents a literal underscore. This query searches for product codes containing an underscore character.

Example 2: Find Text Containing a Literal Percent Sign

SELECT description
FROM products
WHERE description LIKE '%\%%';

This pattern searches for descriptions containing a literal percent sign.

Example 3: Use a Custom Escape Character

SELECT product_code
FROM products
WHERE product_code LIKE '%$_%' ESCAPE '$';

Here, $ is explicitly defined as the escape character. The sequence $_ represents a literal underscore, while the surrounding percent signs remain wildcards.

Important: MySQL normally uses backslash as the default LIKE escape character, unless the NO_BACKSLASH_ESCAPES SQL mode changes that behavior. When writing SQL through application code, account for both SQL string-literal escaping and LIKE pattern escaping.

18. Understanding Case Sensitivity in LIKE

Whether LIKE distinguishes uppercase and lowercase characters depends on the comparison rules, especially the collation of the strings being compared.

Many commonly used MySQL collations are case-insensitive, so patterns such as 'a%' and 'A%' can match the same values. A case-sensitive or binary collation can produce different results.

Example 1: Search Using LIKE

SELECT employee_name
FROM employees
WHERE employee_name LIKE 'amit%';

On a case-insensitive collation, this can match a stored value beginning with Amit as well as amit.

Example 2: Use a Case-Sensitive Collation

SELECT employee_name
FROM employees
WHERE employee_name COLLATE utf8mb4_0900_as_cs
      LIKE 'amit%';

This example uses a case-sensitive collation available in MySQL 8.0 and later. The collation must be compatible with the column's character set. A case-sensitive comparison will distinguish the lowercase pattern from an uppercase initial character.

Check your table and column collations before relying on case-sensitive or case-insensitive matching. Collation names and availability depend on the MySQL version and character set.

19. LIKE and NULL Values

NULL represents a missing or unknown value. If the expression or pattern used by LIKE is NULL, the result is NULL rather than TRUE or FALSE.

Example

SELECT employee_name, email
FROM employees
WHERE email LIKE '%gmail.com';

Employees whose email values are NULL will not appear in the result because the condition is not TRUE for those rows.

Find Missing Email Addresses

SELECT employee_name, email
FROM employees
WHERE email IS NULL;

Use IS NULL to identify missing email addresses. Do not use LIKE or = NULL to check for NULL values.

Include Missing Emails Along with a Pattern

SELECT employee_name, email
FROM employees
WHERE email LIKE '%gmail.com'
   OR email IS NULL;

This query returns rows with Gmail addresses as well as rows whose email field is NULL.

20. LIKE vs. Equality (=)

The equality operator and LIKE are used for different types of comparisons.

Feature	LIKE	Equality (=)
Purpose	Matches a string pattern.	Compares values for equality.
Wildcard support	Supports % and _.	Does not interpret % and _ as LIKE wildcards.
Example	name LIKE 'A%'	name = 'Amit'
Typical use	Partial or pattern-based searches.	Exact value comparisons.

Example Using Equality

SELECT employee_name
FROM employees
WHERE employee_name = 'Amit Sharma';

Example Using LIKE

SELECT employee_name
FROM employees
WHERE employee_name LIKE 'Amit%';

The first query checks for equality with the specified name. The second query matches values that begin with Amit and may contain additional characters.

21. LIKE vs. REGEXP_LIKE()

LIKE is appropriate for straightforward patterns involving prefixes, suffixes, substrings, and fixed character positions. For more advanced pattern matching, MySQL provides regular-expression functionality, including REGEXP_LIKE().

Feature	LIKE	REGEXP_LIKE()
Pattern type	Simple SQL pattern.	Regular expression.
Main wildcards	% and _.	Regular-expression syntax such as ., *, +, character classes, and anchors.
Typical use	Find names beginning with A or text containing a word.	Validate or match more complex patterns.
Pattern matching	The pattern must match the whole string, with % used to allow extra characters.	A match can occur within the string unless the expression uses anchors to constrain it.

LIKE Example

SELECT employee_name
FROM employees
WHERE employee_name LIKE 'A%';

Regular Expression Example

SELECT employee_name
FROM employees
WHERE REGEXP_LIKE(employee_name, '^A');

This regular-expression example matches employee names beginning with A. For advanced use, consult the regular-expression documentation and check the relevant collation and matching options.

22. Using LIKE with UPDATE

LIKE can be used in an UPDATE statement to modify records that match a pattern. Because UPDATE changes data, preview the affected rows before performing the operation.

Example: Update a Department for Matching Job Titles

Suppose an employees table has a department column and you need to update records whose job titles contain a particular phrase.

SELECT employee_id, employee_name, job_title, department
FROM employees
WHERE job_title LIKE '%Developer%';

After verifying the result, an illustrative update could be:

UPDATE employees
SET department = 'Engineering'
WHERE job_title LIKE '%Developer%';

Run this only if the business requirement is to change the department of every matching record. Always verify the target rows and make a backup or use an appropriate transaction when needed.

23. Using LIKE with DELETE

LIKE can also select rows for deletion. This is useful when cleaning test data or removing records that match a known pattern, but it can delete more rows than expected if the pattern is broad.

Example

SELECT *
FROM temporary_customers
WHERE email LIKE '%@example.com';

First inspect the results. If they are exactly the records you intend to remove, the corresponding delete operation is:

DELETE FROM temporary_customers
WHERE email LIKE '%@example.com';

Use this example only when the table and deletion criteria are appropriate. Do not delete production customer records solely because they match a broad email pattern.

24. Performance Considerations and Best Practices

• Use prefix searches when appropriate: A pattern such as 'A%' may allow MySQL to use a suitable B-tree index, depending on the query, collation, and data distribution.
• Understand leading wildcards: A pattern such as '%A' or '%text%' often prevents a conventional B-tree index from being used for a direct range lookup.
• Use the correct operator: Choose LIKE for patterns, = for equality, and REGEXP_LIKE() for more complex regular expressions.
• Check the column collation: Do not assume all LIKE searches are case-sensitive or case-insensitive.
• Escape literal wildcard characters: Escape % and _ when they are part of the stored value rather than intended wildcards.
• Handle NULL explicitly: Use IS NULL or IS NOT NULL when searching for missing values.
• Use selective patterns: Very broad patterns may match many records and increase query costs.
• Use appropriate indexes: Index columns frequently used in prefix searches when indexing is suitable for the workload.
• Use full-text search where appropriate: Large text-search requirements may benefit from MySQL full-text indexing instead of repeated leading-wildcard LIKE queries.
• Test queries on representative data: A query that performs well on a small table may behave differently on a large table.
• Preview data changes: Always run a SELECT query with the same WHERE condition before using LIKE in UPDATE or DELETE.
• Use parameterized queries in applications: Bind user-supplied values safely to avoid SQL injection. Parameterization protects SQL structure, but user-provided wildcard characters may still affect matching behavior unless handled intentionally.

Inspecting a Query Plan

EXPLAIN
SELECT employee_id, employee_name
FROM employees
WHERE employee_name LIKE 'A%';

EXPLAIN shows information about how MySQL plans to execute the query. Check whether a suitable index is available and whether the selected access method is appropriate for the table size and search pattern.

25. Common Mistakes and Troubleshooting

Mistake 1: Forgetting Wildcards

SELECT employee_name
FROM employees
WHERE employee_name LIKE 'A';

Problem: This pattern does not mean "starts with A." It matches the complete one-character string A.

Solution: Use LIKE 'A%' to match strings beginning with A.

Mistake 2: Using the Wrong Wildcard

SELECT employee_name
FROM employees
WHERE employee_name LIKE 'A_';

Problem: The underscore matches exactly one character after A. It does not match any number of characters.

Solution: Use % when the number of additional characters can vary.

Mistake 3: Expecting LIKE to Treat % Literally

SELECT description
FROM products
WHERE description LIKE '%10%%';

Problem: The percent signs are wildcards, so the pattern does not specifically require a literal percent sign after 10.

Solution: Escape the literal percent sign, for example LIKE '%10\%%' under the default backslash escape behavior.

Mistake 4: Assuming Case Sensitivity

Problem: A search for lowercase text may return uppercase text, or vice versa, depending on the collation.

Solution: Check the column collation and use a compatible case-sensitive collation if required.

Mistake 5: Using LIKE to Check for NULL

SELECT *
FROM employees
WHERE email LIKE NULL;

Problem: A comparison with NULL does not identify missing email values.

Solution: Use WHERE email IS NULL.

Mistake 6: Using a Leading Wildcard on a Large Table Without Considering Performance

SELECT *
FROM employees
WHERE employee_name LIKE '%Sharma%';

Problem: A conventional B-tree index usually cannot perform a direct range lookup for a pattern beginning with %.

Solution: Review the query plan and consider full-text search or another search design if substring searches are frequent and the table is large.

26. Practical Project: Employee Pattern Search System

Project Objective: Create a set of queries to search employee records using partial names, email domains, job titles, and character patterns.

Task 1: Find Employees Whose Names Begin with A

SELECT employee_id, employee_name
FROM employees
WHERE employee_name LIKE 'A%';

Task 2: Find Employees with Gmail Addresses

SELECT employee_name, email
FROM employees
WHERE email LIKE '%gmail.com';

Task 3: Find Job Titles Containing Developer

SELECT employee_name, job_title
FROM employees
WHERE job_title LIKE '%Developer%';

Task 4: Find Employees Whose Names Do Not Begin with A

SELECT employee_name
FROM employees
WHERE employee_name NOT LIKE 'A%';

Task 5: Search for Names Beginning with A or R

SELECT employee_name
FROM employees
WHERE employee_name LIKE 'A%'
   OR employee_name LIKE 'R%';

Task 6: Find IT Employees Whose Titles Contain Developer

SELECT employee_name, department, job_title
FROM employees
WHERE department = 'IT'
  AND job_title LIKE '%Developer%';

Task 7: Count Matching Email Addresses

SELECT COUNT(*) AS matching_emails
FROM employees
WHERE email LIKE '%gmail.com';

Task 8: Find Unique Cities Beginning with M

SELECT DISTINCT city
FROM employees
WHERE city LIKE 'M%';

Expected Learning Outcome

After completing the project, you should be able to use LIKE for partial text searches, combine multiple patterns, exclude matches, count matching records, and retrieve distinct values.

27. Interview Questions and Answers

Q1. What is the LIKE operator in MySQL?

LIKE is a comparison operator that checks whether a string matches a specified SQL pattern.

Q2. Which wildcards does LIKE support?

The two primary SQL LIKE wildcards are % for zero or more characters and _ for exactly one character.

Q3. How do you find names beginning with A?

SELECT *
FROM employees
WHERE employee_name LIKE 'A%';

Q4. How do you find names ending with a particular word?

SELECT *
FROM employees
WHERE employee_name LIKE '%word';

Q5. How do you find values containing a word anywhere?

SELECT *
FROM employees
WHERE employee_name LIKE '%word%';

Q6. What is the difference between % and _?

% matches zero or more characters. _ matches exactly one character.

Q7. What is NOT LIKE used for?

NOT LIKE finds values that do not match a specified pattern. Rows where the tested value is NULL are not returned unless NULL is handled separately.

Q8. Is LIKE case-sensitive?

It depends on the collation and comparison rules. Many common MySQL collations are case-insensitive, while case-sensitive collations distinguish uppercase and lowercase characters.

Q9. How can you search for a literal underscore?

Escape the underscore so that it is treated as a literal character, for example LIKE '%\_%' when the default backslash escape behavior is enabled.

Q10. Can LIKE be used with UPDATE and DELETE?

Yes. LIKE can filter rows in UPDATE and DELETE statements. Verify the matching rows before changing or deleting data.

Q11. Why can LIKE '%text%' be slow?

A leading wildcard often prevents a conventional B-tree index from being used for a direct range lookup. Large datasets may need a different search strategy.

Q12. What is the difference between LIKE and REGEXP_LIKE()?

LIKE supports simple patterns using % and _. REGEXP_LIKE() supports more advanced regular-expression patterns.

Q13. How do you find email addresses ending with yahoo.com?

SELECT employee_name, email
FROM employees
WHERE email LIKE '%yahoo.com';

Q14. What happens if the column contains NULL?

LIKE evaluates to NULL when the tested expression or pattern is NULL. Such a row does not pass a normal WHERE condition unless another condition explicitly includes it.

Q15. How do you find a string with exactly five characters?

SELECT code
FROM product_codes
WHERE code LIKE '_____';

This example assumes a table containing a code column. Each underscore represents exactly one character.

28. Practice Exercises

Use the sample employees table to complete the following exercises.

1. Display all employees whose names begin with A.
2. Find employees whose email addresses end with gmail.com.
3. Retrieve job titles containing the word Developer.
4. Display employees whose names do not begin with R.
5. Find employees whose names begin with A or S.
6. Retrieve IT employees whose job titles contain Developer.
7. Count employees whose email addresses end with yahoo.com.
8. Display unique cities beginning with M.
9. Write a query to search for a literal underscore in a product code.
10. Explain why the pattern 'A_' behaves differently from 'A%'.
11. Explain how collation affects case sensitivity in LIKE comparisons.
12. Write a query to find job titles that do not contain Manager.

29. Practice Exercise Solutions

Solution 1: Names Beginning with A

SELECT employee_name
FROM employees
WHERE employee_name LIKE 'A%';

Solution 2: Gmail Addresses

SELECT employee_name, email
FROM employees
WHERE email LIKE '%gmail.com';

Solution 3: Job Titles Containing Developer

SELECT employee_name, job_title
FROM employees
WHERE job_title LIKE '%Developer%';

Solution 4: Names Not Beginning with R

SELECT employee_name
FROM employees
WHERE employee_name NOT LIKE 'R%';

Solution 5: Names Beginning with A or S

SELECT employee_name
FROM employees
WHERE employee_name LIKE 'A%'
   OR employee_name LIKE 'S%';

Solution 6: IT Developers

SELECT employee_name, job_title
FROM employees
WHERE department = 'IT'
  AND job_title LIKE '%Developer%';

Solution 7: Yahoo Email Count

SELECT COUNT(*) AS yahoo_email_count
FROM employees
WHERE email LIKE '%yahoo.com';

Solution 8: Unique Cities Beginning with M

SELECT DISTINCT city
FROM employees
WHERE city LIKE 'M%';

Solution 9: Find a Literal Underscore

SELECT product_code
FROM products
WHERE product_code LIKE '%\_%';

This query uses the default backslash escape behavior to search for a literal underscore. If NO_BACKSLASH_ESCAPES is enabled, use an appropriate explicit ESCAPE clause or adjust the pattern accordingly.

Solution 10: Difference Between A_ and A%

'A_' matches a two-character string beginning with A, while 'A%' matches a string beginning with A followed by zero or more characters.

Solution 11: Case Sensitivity

LIKE follows the applicable string comparison and collation rules. A case-insensitive collation may match both uppercase and lowercase letters, while a case-sensitive collation distinguishes them.

Solution 12: Job Titles Without Manager

SELECT employee_name, job_title
FROM employees
WHERE job_title NOT LIKE '%Manager%';

30. Quick Revision Checklist

• LIKE is used for pattern-based string searching in MySQL.
• % matches zero or more characters.
• _ matches exactly one character.
• 'A%' searches for values beginning with A.
• '%A' searches for values ending with A.
• '%A%' searches for values containing A.
• NOT LIKE excludes values matching a pattern.
• AND requires all conditions to be true; OR allows at least one condition to be true.
• Escape % and _ when searching for literal wildcard characters.
• Case sensitivity depends on the applicable collation and comparison rules.
• NULL values require explicit IS NULL or IS NOT NULL checks.
• Leading wildcards can make conventional index-based searches less efficient.
• Use REGEXP_LIKE() for more advanced regular-expression matching.
• Preview records before using LIKE in UPDATE or DELETE statements.

31. Official Documentation and Further Learning

Use the following resources to learn more about MySQL pattern matching and string comparisons.

• MySQL Pattern Matching Documentation – Learn about LIKE patterns and regular-expression matching.
• MySQL String Comparison Functions and Operators – Understand LIKE, NOT LIKE, escaping, and string comparison behavior.
• MySQL String Literals – Learn about escape sequences in SQL strings.

32. Conclusion

The LIKE operator is a fundamental MySQL feature for searching text based on patterns rather than exact values. The percent (%) wildcard matches zero or more characters, while the underscore (_) wildcard matches exactly one character. Together, these wildcards support prefix searches, suffix searches, substring searches, and fixed-position patterns.

You can combine LIKE with NOT LIKE, AND, OR, DISTINCT, ORDER BY, COUNT(), UPDATE, and DELETE to handle a wide range of database tasks. For reliable results, understand your column's collation, escape literal wildcard characters, handle NULL values explicitly, and consider performance when searching large tables.

Continue practising these examples through the MySQL Training Course. You can also register for a course demo to learn more about the training.